/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package introse;

import com.sun.net.ssl.internal.ssl.Debug;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class transaction {

    private int transactionNumber;
    private String invoiceNumber;
    private Timestamp dateAndTime;
    private String clientName;
    private String remark;
    private String sourceName;
    private float totalAmount;
    private List<purchasedProduct> purchasedProductsList = new ArrayList();

    public transaction(int transactionNumber, String invoiceNumber, Timestamp dateAndTime, String clientName, String remark, String sourceName, float totalAmount) {
        this.transactionNumber = transactionNumber;
        this.invoiceNumber = invoiceNumber;
        this.dateAndTime = dateAndTime;
        this.clientName = clientName;
        this.remark = remark;
        this.sourceName = sourceName;
        this.totalAmount = totalAmount;

        loadPurchasedProducts();
    }

    public final void loadPurchasedProducts() {
        try {
            databaseConn conn = new databaseConn();
//            ResultSet purchasedProducts = conn.sqlRead("select purchasedproduct.productNumber, quantity, productName, isGasul, isActive, price, dateAndTime, MAX(`dateAndTime`) as maxDate from `purchasedproduct`"
//                    + " left join product on product.productNumber = purchasedproduct.ProductNumber"
//                    + " left join productpricehistory on productpricehistory.productNumber = product.productNumber"
//                    + " where `purchasedproduct`.transactionNumber = " + Integer.toString(transactionNumber)
//                    + " and `productpricehistory`.dateAndTime <= (select dateAndTime from transaction where transactionNumber = " + Integer.toString(transactionNumber) +  ")"
//                    + " group by productNumber order by dateAndTime DESC");
            ResultSet purchasedProducts = conn.sqlRead(String.format("select purchasedproduct.productNumber, quantity, productName, isGasul, isActive, price, dateAndTime"
                    + " from `purchasedproduct` "
                    + " left join product on product.productNumber = purchasedproduct.ProductNumber "
                    + " left join productpricehistory on productpricehistory.productNumber = product.productNumber "
                    + " where `purchasedproduct`.transactionNumber = %d and `productpricehistory`.dateAndTime ="
                    + " (select max(`dateAndTime`)"
                    + " from `purchasedproduct` "
                    + " left join productpricehistory on productpricehistory.productNumber = `purchasedproduct`.productNumber "
                    + " where `purchasedproduct`.transactionNumber = %d and `productpricehistory`.dateAndTime <= "
                    + " (select dateAndTime from transaction where transactionNumber = %d))", transactionNumber, transactionNumber, transactionNumber));
            List<purchasedProduct> tempPurchasedProductList = new ArrayList();
            while (purchasedProducts.next()) {
                tempPurchasedProductList.add(new purchasedProduct(purchasedProducts.getInt("productNumber"), purchasedProducts.getString("productName"), purchasedProducts.getFloat("price"), purchasedProducts.getBoolean("isActive"), purchasedProducts.getBoolean("isGasul"), purchasedProducts.getInt("quantity")));
            }
            this.purchasedProductsList = tempPurchasedProductList;
            
            this.totalAmount = 0;
            for (purchasedProduct prod : getPurchasedProducts()) {
                this.totalAmount += prod.getTotalPrice();
            }
        } catch (SQLException ex) {
            Debug.println("", ex.toString());
        }
    }

    public void deleteTransaction() {
        databaseConn conn = new databaseConn();
        conn.sqlStatement(String.format("DELETE FROM `sashmirgasul`.`purchasedproduct` WHERE `transactionNumber`='%d';", getTransactionNumber()));
        conn.sqlStatement(String.format("DELETE FROM `sashmirgasul`.`transaction` WHERE `transactionNumber`='%d';", getTransactionNumber()));
    }

    public List<purchasedProduct> getPurchasedProducts() {
        return this.purchasedProductsList;
    }

    public int getTransactionNumber() {
        return this.transactionNumber;
    }

    public String getInvoiceNumber() {
        return this.invoiceNumber;
    }

    public Timestamp getDateAndTime() {
        return this.dateAndTime;
    }

    public String getClientName() {
        return this.clientName;
    }

    public String getRemark() {
        return this.remark;
    }

    public String getSourceName() {
        return this.sourceName;
    }

    public float getTotalAmount() {
        return this.totalAmount;
    }
}
